﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using DotNetNuke.Entities.Tabs;
using DotNetNuke.Entities.Modules;

using iFinity.PropertyAgent.ModuleFriendlyUrlProvider.Entities;

namespace iFinity.PropertyAgent.ModuleFriendlyUrlProvider.Data
{
    internal class SqlDataProvider : DataProvider 
    {
        private const string ModuleQualifier = "pap_";
        private const string OwnerQualifier = "ifty_";

        #region Private Members
        private const string ProviderType = "data";
        private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private string _connectionString;
        private string _providerPath;
        private string _objectQualifier;
        private string _databaseOwner;
        #endregion
        #region Constructor
        /// <summary>
        /// Constructs new SqlDataProvider instance
        /// </summary>
        internal SqlDataProvider()
        {
            //Read the configuration specific information for this provider
            Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];

            //Read the attributes for this provider
            //Get Connection string from web.config
            _connectionString = Config.GetConnectionString();

            if (_connectionString.Length == 0)
            {
                // Use connection string specified in provider
                _connectionString = objProvider.Attributes["connectionString"];
            }

            _providerPath = objProvider.Attributes["providerPath"];

            //override the standard dotNetNuke qualifier with a iFinity one if it exists
            _objectQualifier = objProvider.Attributes["objectQualifier"];
            if ((_objectQualifier != "") && (_objectQualifier.EndsWith("_") == false))
            {
                _objectQualifier += "_";
            }
            else
                if (_objectQualifier == null) _objectQualifier = "";

            _objectQualifier += OwnerQualifier;

            _databaseOwner = objProvider.Attributes["databaseOwner"];
            if ((_databaseOwner != "") && (_databaseOwner.EndsWith(".") == false))
            {
                _databaseOwner += ".";
            }
        }
        #endregion
        #region Properties

        /// <summary>
        /// Gets and sets the connection string
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }
        /// <summary>
        /// Gets and sets the Provider path
        /// </summary>
        public string ProviderPath
        {
            get { return _providerPath; }
        }
        /// <summary>
        /// Gets and sets the Object qualifier
        /// </summary>
        public string ObjectQualifier
        {
            get { return _objectQualifier; }
        }
        /// <summary>
        /// Gets and sets the database ownere
        /// </summary>
        public string DatabaseOwner
        {
            get { return _databaseOwner; }
        }

        #endregion
        #region private members
        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Gets the fully qualified name of the stored procedure
        /// </summary>
        /// <param name="name">The name of the stored procedure</param>
        /// <returns>The fully qualified name</returns>
        /// -----------------------------------------------------------------------------
        private string GetFullyQualifiedName(string name)
        {
            return DatabaseOwner + ObjectQualifier + ModuleQualifier + name;
        }
        #endregion

        #region abstract overridden properties
        internal override void GetPropertyAgentItemsForTab(int tabId, TabUrlOptions urlOptions,  PropertyAgentOptions paOptions, out FriendlyUrlInfoCol urls)
        {
            urls = new FriendlyUrlInfoCol();

            string sp = GetFullyQualifiedName("GetPropertyAgentEntriesForTab");
            //@tabId int, @startingPropertyId int, @customFields varchar(255), @ptUrlType int
            SqlParameter[] parms = new SqlParameter[4];
            parms[0] = new SqlParameter("@TabId", tabId);
            if (urlOptions != null)
                parms[1] = new SqlParameter("@startingPropertyId", urlOptions.StartingPropertyId);
            else
                parms[1] = new SqlParameter("@startingPropertyId", 0);
            string customFieldIds = string.Join( ",", urlOptions.CustomFieldIds.ToArray());
            parms[2] = new SqlParameter("@customFields", customFieldIds);
            parms[3] = new SqlParameter("@ptUrlType", (int)urlOptions.PropertyTypeSource);

            //call the db
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);

            //bind the results into a collection of urls
            BindReaderToUrls(rdr, urlOptions, paOptions, ref urls);

            rdr.Close();
            rdr.Dispose();
        }
        internal override Dictionary<string, string> GetPropertyAgentCustomFields(int tabId)
        {
            //[dnn_ifty_pap_GetPropertyAgentCustomFields]
            Dictionary<string, string> customFields = new Dictionary<string, string>();
            string sp = GetFullyQualifiedName("GetPropertyAgentCustomFields");
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter("@TabId", tabId);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            while (rdr.Read())
            {
                int customFieldId = GetSafeInt(rdr, "CustomFieldId",0);
                string caption = GetSafeString(rdr, "Caption");
                if (customFieldId > 0)
                {
                    customFields.Add(customFieldId.ToString(), caption);
                }
            }
            rdr.Close();
            rdr.Dispose();
            return customFields;
            
        }
        internal override List<PropertyAgentOptions> GetPropertyAgentSettings(int portalId)
        {
            List<PropertyAgentOptions> settings = new List<PropertyAgentOptions>();
            string sp = GetFullyQualifiedName("GetPropertyAgentSettings");
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter("@PortalId", portalId);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            int lastTabId = -1;
            PropertyAgentOptions pas = null;
            while (rdr.Read())
            {
                int tabId = GetSafeInt(rdr, "TabId", -1);
                int moduleId = GetSafeInt(rdr, "ModuleId", -1);
                if (tabId != lastTabId)
                {
                    //change in tab, new options
                    if (pas != null)//add to list
                        settings.Add(pas);
                    //new object
                    pas = new PropertyAgentOptions();
                    pas.TabId = tabId;
                    pas.PortalId = portalId;
                    pas.ModuleId = moduleId;
                }
                if (tabId > -1)
                {
                    string settingName = GetSafeString(rdr, "SettingName");
                    string settingValue = GetSafeString(rdr, "SettingValue");
                    switch (settingName.ToLower())
                    {
                        case "seoagenttype":
                            pas.AgentTypeName = settingValue;
                            break;
                        case "seopropertyid":
                            pas.PropertyIdName = settingValue;
                            break;
                        case "seopropertytypeid":
                            pas.PropertyTypeIdName = settingValue;
                            break;
                        case "seotitlereplacement":
                            if (settingValue.ToLower() == "dash")
                                pas.TitleReplacement = "-";
                            else
                                pas.TitleReplacement = "_";
                            break;
                        case "seoviewpropertytitle":
                            //ignoreing these for the moment
                            break;
                        case "seoviewtypetitle":
                            //ignoring these for the moment
                            break;
                    }
                }
                lastTabId = tabId;
            }
            //add the last one
            if (pas != null)
            {
                settings.Add(pas);
            }

            rdr.Close();
            rdr.Dispose();
            return settings;
        }




        internal override FriendlyUrlInfo GetPropertyAgentItem(int itemId, string itemType, TabUrlOptions urlOptions, PropertyAgentOptions paOptions, int tabId)
        {
            FriendlyUrlInfo fuf = null;
            //[dnn_ifty_pap_GetPropertyAgentEntry]
            string sp = GetFullyQualifiedName("GetPropertyAgentEntry");
            SqlParameter[] parms = new SqlParameter[5];
            parms[0] = new SqlParameter("@TabId", tabId);
            parms[1] = new SqlParameter("@startingPropertyId", urlOptions.StartingPropertyId);
            string customFieldIds = string.Join(",", urlOptions.CustomFieldIds.ToArray());
            parms[2] = new SqlParameter("@customFields", customFieldIds);
            parms[3] = new SqlParameter("@ptUrlType", (int)urlOptions.PropertyTypeSource);
            parms[4] = new SqlParameter("@itemId", itemId);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            FriendlyUrlInfoCol urls = new FriendlyUrlInfoCol();
            BindReaderToUrls(rdr, urlOptions, paOptions, ref urls);
            
            rdr.Close();
            rdr.Dispose();
            if (urls != null && urls.Count > 0)
                fuf = urls[0];
            else
                fuf = null;
            return fuf;
        }
        internal string GetSafeString(SqlDataReader rdr, string colName)
        {
            string result = "";
            if (colName != null && colName != "" && !Convert.IsDBNull(rdr[colName]))
                result = (string)rdr[colName];
            return result;
        }
        private int GetSafeInt(SqlDataReader rdr, string colName, int defaultValue)
        {
            int result = defaultValue;
            if (colName != null && colName != "" && !Convert.IsDBNull(rdr[colName]))
                result = (int)rdr[colName];
            return result;
        }
        private void BindReaderToUrls(SqlDataReader rdr, TabUrlOptions urlOptions, PropertyAgentOptions paOptions, ref FriendlyUrlInfoCol urls)
        {
            //work out which url fragment to use
            int lastItemId = -1;
            string lastItemType = null;
            string thisItemType = null;
            int thisItemId = -2;
            FriendlyUrlInfo fuf = null;
            
            while (rdr.Read())
            {
                thisItemId = (int)rdr["itemId"];
                thisItemType = GetSafeString(rdr, "ItemType");
                if (lastItemId != thisItemId || lastItemType != thisItemType)
                {
                    //change in items
                    if (fuf != null)
                    {
                        urls.Add(fuf);
                    }
                    //create new object and bind to results
                    fuf = new FriendlyUrlInfo();
                    BindReaderToFriendlyUrl(ref fuf, rdr, urlOptions);
                }
                else
                {
                    //same item - just add fragment on end
                    string fragment = GetSafeString(rdr, "UrlFragment");
                    if (string.IsNullOrEmpty(fragment) == false)
                        fuf.urlFragment += paOptions.TitleReplacement + fragment;
                }

                lastItemId = fuf.itemId;
                lastItemType = thisItemType;
            }
            if (fuf != null)
                urls.Add(fuf);

        }
        private void BindReaderToFriendlyUrl(ref FriendlyUrlInfo url, SqlDataReader rdr, TabUrlOptions urlOptions)
        {
            if (url == null) url = new FriendlyUrlInfo();
            if (!Convert.IsDBNull(rdr["ItemId"]))
                url.itemId  = (int)rdr["ItemId"];

            if (!Convert.IsDBNull(rdr["ParentId"]))
                url.parentId = (int)rdr["ParentId"];
            else
                url.parentId = -1;

            string itemType = "";
            if (!Convert.IsDBNull(rdr["ItemType"]))
                itemType = (string)rdr["ItemType"];

            //the date is a general purpose field that is context-dependent on the item type
            if (!Convert.IsDBNull(rdr["urlDate"]))
                url.urlDate = (DateTime)rdr["urlDate"];
            else
                url.urlDate = DateTime.MinValue;

            //the num is a general purpose field that is context-dependent on the item Type
            url.urlNum = (int)rdr["UrlNum"];

            url.itemType = itemType;

            url.urlFragment = GetSafeString(rdr, "UrlFragment");

        }
        #endregion
    }
}
